import requests
import datetime
import xlsxwriter
import time
import pymysql
from DBUtils.PooledDB import PooledDB
import threading
import math
import pandas as pd
from time import *


ID = "ww7b4086d88a144eec"
SECRET = "QqZ9Au1EwEDc1PyvcZaAgJBhaPDeK1z5zinENKHLL1Q"

def pool():
    pool = PooledDB(pymysql, 15, host='localhost', user='root', passwd='zcxvbmn', db='weixindb', port=3306)
    return pool

# 获取access_token
url_get_access_token = "https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=" + ID + "&corpsecret=" + SECRET
def get_access_token(ID,SECRET):
    access_token_json = requests.get(url_get_access_token).json()
    print(access_token_json)
    while access_token_json['errcode'] != 0:
        get_access_token(ID, SECRET)
        time.sleep(2)
    access_token = access_token_json['access_token']
    return access_token

# access_token缓存
access_token = get_access_token(ID,SECRET)


# 获取企业全部的发表列表(个人的从这里拿)
url_get_moment_list = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/get_moment_list?access_token=" + access_token
def clean_and_get_moment_list(start_time, end_time, type):
    delete_sql = "delete from moment_list"
    db = pool().connection()
    cursor = db.cursor()
    cursor.execute(delete_sql)
    i = 1
    next_cursor = ''
    while i == 1 or next_cursor != '':
        moment_publish_inform = {
            "start_time": start_time,
            "end_time": end_time,
            "filter_type": type,#type字段 0为企业发表，1为个人发表，2为所有
            "cursor": next_cursor,
            "limit": 20
        }
        moment_publish = requests.post(url_get_moment_list, json=moment_publish_inform)
        moment_list = moment_publish.json()['moment_list'] #moment_list是个列表
        tuple_moment_list = []
        insert_sql = ""
        for moment in moment_list:
            moment_id = (moment['moment_id'],)
            try:
                creator = (moment['creator'],)
            except:
                creator = ("/",)
            create_type = (moment['create_type'],)
            visible_type = (moment['visible_type'],)
            try:
                text = (moment['text']['content'][:50],)
            except:
                text = ("/",)
            try:
                media_id = moment['video']['media_id']
                video = ("是",)
            except:
                video = ("否",)
            tuple_moment = moment_id + creator + create_type + visible_type + text + video
            insert_sql = "insert into moment_list(moment_id,creator,create_type,visible_type,text,video) values(%s,%s,%s,%s,%s,%s)"
            tuple_moment_list.append(tuple_moment)
        cursor.executemany(insert_sql, tuple_moment_list)
        db.commit()
        next_cursor = moment_publish.json()['next_cursor']
        i = i + 1
    db.close()
    return

#获取可发送顾客数(获取朋友圈可见范围)
def get_customer_all_count(moment_id, userid):
    url_get_moment_customer_list = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/get_moment_customer_list" \
                                   "?access_token=" + access_token
    i = 1
    next_cursor = ''
    customer_all_count = 0
    while i == 1 or next_cursor != '':
        moment_id_userid_json = {
            "moment_id": moment_id,
            "userid": userid,
            "cursor": next_cursor,
            "limit": 1000
        }
        customer_all = requests.post(url_get_moment_customer_list, json=moment_id_userid_json)
        customer_all_count = customer_all_count + len(customer_all.json()['customer_list'])
        try:
            next_cursor = customer_all.json()['next_cursor']
        except:
            next_cursor = ''
        i = i + 1
    return customer_all_count

#获取触达顾客数(可见顾客列表)
def get_customer_sended_count(moment_id, userid):
    url_get_moment_send_result = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/get_moment_send_result?access_token="+ access_token
    i = 1
    next_cursor = ''
    customer_sended_count = 0
    while i == 1 or next_cursor != '':
        moment_id_userid_json = {
            "moment_id": moment_id,
            "userid": userid,
            "cursor": next_cursor,
            "limit": 1000
        }
        customer_sended = requests.post(url_get_moment_send_result, json=moment_id_userid_json)
        customer_sended_count = customer_sended_count + len(customer_sended.json()['customer_list'])
        try:
            next_cursor = customer_sended.json()['next_cursor']
        except:
            next_cursor = ''
        i = i + 1
    return customer_sended_count

def get_moment_list_from_mysql():
    db = pool().connection()
    cursor = db.cursor()
    delete_sql = "delete from moment_publish_result"
    cursor.execute(delete_sql)
    db.commit()
    select_moment_sql = "select * from moment_list"
    cursor.execute(select_moment_sql)
    db.commit()
    moment_list = cursor.fetchall()
    return moment_list

# 思路是先拿到moment_list，先分开个人的发布与企业发布，【分别形成个人的moment_list和企业的moment_list】，
# 个人moment_list的用个人的task，企业moment_list发布用企业的task，【分别】threading执行

def renew_moment_list():
    moment_list = get_moment_list_from_mysql()
    #print(moment_list) #这个是元组
    moment_list_1 = () #个人moment列表
    moment_list_0 = () #企业moment列表
    for moment in moment_list:
        create_type = moment[2]
        if create_type == '1': #个人发表，执行个人task
            moment_list_1 = moment_list_1 + (moment,)
        if create_type == '0':
            moment_list_0 = moment_list_0 + (moment,)
    moment_list_renew = [moment_list_0, moment_list_1]
    #print(moment_list_renew[1])
    return moment_list_renew


url_get_moment_task = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/get_moment_task?access_token=" + access_token

#拿企业发表任务的列表
def get_publish_task_0():
    moment_list_0 = renew_moment_list()[0]
    moment_task_list_0 = []
    for moment_0 in moment_list_0:
        moment_id = moment_0[0]
        creator = moment_0[1]
        create_type = moment_0[2]
        visible_type = moment_0[3]
        text = moment_0[4]
        video = moment_0[5]
        i = 1
        next_cursor = ''
        while i == 1 or next_cursor != '':
            moment_id_json = {
                "moment_id": moment_id,
                "cursor": next_cursor,
                "limit": 1000
            }
            moment_task_a = requests.post(url_get_moment_task, json=moment_id_json)
            moment_task_list_0_a = moment_task_a.json()["task_list"]
            for moment_task in moment_task_list_0_a:
                moment_task['moment_id'] = moment_id
                moment_task['creator'] = creator
                moment_task['create_type'] = create_type
                moment_task['visible_type'] = visible_type
                moment_task['text'] = text
                moment_task['video'] = video
                moment_task_list_0.append(moment_task)
            #print(moment_task_list_0_a)
            #print(type(moment_task_list_0_a))
            #print(moment_task_list_0)
            try:
                next_cursor = moment_task_a.json()["next_cursor"]
            except:
                next_cursor = ''
            i = i + 1
    #print(moment_task_list_0)
    #print(len(moment_task_list_0))
    return moment_task_list_0

# 拿个人发表任务的列表(全)
def get_publish_task_1():
    moment_list_1 = renew_moment_list()[1]
    #print(moment_list_1)
    moment_task_list_1 = []
    i = 0
    for moment_1 in moment_list_1:
        moment_task = {}
        #print(moment_1)
        #print(moment_1[0])
        moment_task['moment_id'] = moment_1[0]
        moment_task['creator'] = moment_1[1]
        moment_task['create_type'] = moment_1[2]
        moment_task['visible_type'] = moment_1[3]
        moment_task['text'] = moment_1[4]
        moment_task['video'] = moment_1[5]
        #print(moment_task)
        moment_task_list_1.append(moment_task)
        #print(moment_task_list_1[i-1])
        i = i + 1
    #print(moment_task_list_1[1111:1115])
    return moment_task_list_1

#拿企业任务执行触达情况(多线程中单条任务)
def get_publish_result_task_0(start_num,end_num):
    #global customer_sended_count, customer_all_count
    db = pool().connection()
    cursor = db.cursor()
    for publish_task in publish_task_list_0[start_num: end_num]:
        moment_id = publish_task['moment_id']
        userid = publish_task['userid']
        creator = publish_task['creator']
        create_type = publish_task['create_type']
        visible_type = publish_task['visible_type']
        video = publish_task['video']
        text = publish_task['text']
        publish_status = publish_task['publish_status']
        if publish_status == 0:
            customer_all_count = get_customer_all_count(moment_id, userid)
            customer_sended_count = 0
        elif publish_status == 1:
            customer_all_count = get_customer_all_count(moment_id, userid)
            customer_sended_count = get_customer_sended_count(moment_id, userid)
        insert_moment_publish_sql = "insert into moment_publish_result \
                            (moment_id,creator,create_type,visible_type,text,video,userid,publish_status,customer_all_count," \
                                    "customer_sended_count) \
        values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" \
                                    % (
                                        repr(moment_id), repr(creator), create_type, visible_type, repr(text),
                                        repr(video), repr(userid), publish_status, customer_all_count,
                                        customer_sended_count)
        cursor.execute(insert_moment_publish_sql)
        db.commit()
    return

#拿个人发送触达情况(多线程中单条任务)
def get_publish_result_task_1(start_num,end_num):
    #global customer_sended_count, customer_all_count
    db = pool().connection()
    cursor = db.cursor()
    for publish_task in publish_task_list_1[start_num: end_num]:
        #print(publish_task_list_1[start_num: end_num])
        moment_id = publish_task['moment_id']
        userid = publish_task['creator']
        creator = publish_task['creator']
        create_type = publish_task['create_type']
        visible_type = publish_task['visible_type']
        video = publish_task['video']
        text = publish_task['text']
        publish_status = 1
        customer_all_count = get_customer_all_count(moment_id, userid)
        customer_sended_count = get_customer_sended_count(moment_id, userid)
        insert_moment_publish_sql = "insert into moment_publish_result(moment_id,creator,create_type,visible_type,text,video,userid,publish_status,customer_all_count,customer_sended_count)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" % (
        repr(moment_id), repr(creator), create_type, visible_type, repr(text), repr(video), repr(userid),
        publish_status, customer_all_count, customer_sended_count)
        cursor.execute(insert_moment_publish_sql)
        db.commit()
    return

# 企业任务触达多线程
def get_publish_result_threading_task_0():
    try:
        threading_list = []
        for n in range(0, len(publish_task_list_0), int(len(publish_task_list_0)/6)):
            start_num = n
            end_num = n+int(len(publish_task_list_0)/6)-1
            task_threading = threading.Thread(target=get_publish_result_task_0, args=(start_num, end_num))
            threading_list.append(task_threading)
        for task in threading_list:
            task.setDaemon(True)
            task.start()
        for task in threading_list:
            task.join()
    except:
        print("该时间段没有企业朋友圈推送")
    return

# 个人推送触达多线程
def get_publish_result_threading_task_1():
    try:
        threading_list = []
        for n in range(0, len(publish_task_list_1), int(len(publish_task_list_1)/6)):
            start_num = n
            end_num = n+int(len(publish_task_list_1)/6)-1
            task_threading = threading.Thread(target=get_publish_result_task_1, args=(start_num, end_num))
            threading_list.append(task_threading)
        for task in threading_list:
            task.setDaemon(True)
            task.start()
        for task in threading_list:
            task.join()
    except:
        print('该时间段没有个人朋友圈推送')
    return

#不含有多线程的拿触达
'''
def clean_and_get_moment_publish_result():
    global customer_sended_count, customer_all_count
    db = pool().connection()
    cursor = db.cursor()
    delete_sql = "delete from moment_publish_result"
    cursor.execute(delete_sql)
    db.commit()
    select_moment_sql = "select * from moment_list"
    cursor.execute(select_moment_sql)
    db.commit()
    moment_list = cursor.fetchall()  # 返回的是元组
    print(moment_list)
    for moment in moment_list:
        moment_id = moment[0]
        creator = moment[1]
        create_type = moment[2]
        visible_type = moment[3]
        text = moment[4]
        video = moment[5]
        if create_type == '1': #个人发送
            userid = creator
            publish_status = 1
            customer_all_count = get_customer_all_count(moment_id, userid)
            customer_sended_count = get_customer_sended_count(moment_id, userid)
            insert_moment_publish_sql = "insert into moment_publish_result(moment_id,creator,create_type,visible_type,text,video,userid,publish_status,customer_all_count,customer_sended_count)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"%(repr(moment_id), repr(creator), create_type, visible_type, repr(text), repr(video), repr(userid), publish_status, customer_all_count,customer_sended_count)
            cursor.execute(insert_moment_publish_sql)
            db.commit()
        elif create_type == '0': #企业发送
            url_get_moment_task = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/get_moment_task?access_token=" + get_access_token(
                ID, SECRET)
            i = 1
            next_cursor = ''
            while i == 1 or next_cursor != '':
                moment_id_json = {
                    "moment_id": moment_id,
                    "cursor": next_cursor,
                    "limit": 1000
                }
                moment_task_a = requests.post(url_get_moment_task, json=moment_id_json)
                moment_task_list = moment_task_a.json()["task_list"]
                # 这里可以用多线程 多个userid并行
                for moment_task in moment_task_list:
                    userid = moment_task['userid']
                    publish_status = moment_task['publish_status']
                    if publish_status == 0:
                        customer_all_count = get_customer_all_count(moment_id, userid)
                        customer_sended_count = 0
                    elif publish_status == 1:
                        customer_all_count = get_customer_all_count(moment_id, userid)
                        customer_sended_count = get_customer_sended_count(moment_id, userid)
                    insert_moment_publish_sql = "insert into moment_publish_result \
                    (moment_id,creator,create_type,visible_type,text,video,userid,publish_status,customer_all_count," \
                                                "customer_sended_count) \
                    values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" \
                                                % (
                                                repr(moment_id), repr(creator), create_type, visible_type, repr(text),
                                                repr(video), repr(userid), publish_status, customer_all_count,
                                                customer_sended_count)
                    cursor.execute(insert_moment_publish_sql)
                    db.commit()
                try:
                    next_cursor = moment_task_a.json()["next_cursor"]
                except:
                    next_cursor = ''
                i = i+1
'''

# 获取数据库整表转为dataframe
def get_moment_list_df():
    db = pool().connection()
    cursor = db.cursor()
    sql = "select * from moment_list"
    cursor.execute(sql)
    moment_list = cursor.fetchall()
    moment_list_df = pd.DataFrame(list(moment_list),
                                    columns=['朋友圈id', '员工工号', '发送方(1为个人)', '可见方式', '文案(前50字)', '有视频'])
    db.close()
    return moment_list_df

def get_moment_publish_result_df():
    db = pool().connection()
    cursor = db.cursor()
    sql = "select * from moment_publish_result"
    cursor.execute(sql)
    moment_publish_result = cursor.fetchall()
    moment_publish_result_df = pd.DataFrame(list(moment_publish_result),
                                    columns=['朋友圈id', '发送员工工号', '群发方式', '可见方式', '文案(前50字)', '有视频', '员工工号', '是否发送', '可发人数', '触达人数'])
    db.close()
    return moment_publish_result_df
print("开始拉取朋友圈发布记录")
# 参数从左到右:开始时间,结束时间,发布类型,0为企业发表，1为个人发表，2为所有
clean_and_get_moment_list(1733241600, 1733328000, 0)
print('开始从数据库拿企业发布任务')
publish_task_list_0 = get_publish_task_0()
print('从数据库拿企业发布任务结束')
print('开始从数据库拿个人发布任务')
publish_task_list_1 = get_publish_task_1()
print(publish_task_list_1)
print('从数据库拿个人发布任务结束')

print('开始企业任务触达执行多线程')
get_publish_result_threading_task_0()
print('企业任务触达执行多线程结束')
print('开始个人推送触达执行多线程')
get_publish_result_threading_task_1()
print('个人推送触达执行多线程结束')
print('所有数据拉取任务执行完毕')
print('开始输出excel')
# 输出为excel表格
if len(publish_task_list_0)+len(publish_task_list_1)>0:
    with pd.ExcelWriter('D:\企微朋友圈发表记录%s.xlsx' % (strftime('%Y%m%d%H%M%S', localtime(time())))) as writer:
        get_moment_list_df().to_excel(writer, engine='xlsxwriter', sheet_name='企微朋友圈发表记录')
        get_moment_publish_result_df().to_excel(writer, engine='xlsxwriter', sheet_name='触达明细')
else:
    print('该时间段没有满足条件的朋友圈')
print('完工~')